/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author AMP
 */
public class DBClothing implements IFDBClothing
{
	private  Connection con;

	public DBClothing()
    {
      con = DBConnection.getInstance().getDBcon();
    }

	public int deleteClothing(String barcode)
    {
        int rc=-1;

        String query="DELETE FROM product WHERE barcode = '" +
                        barcode + "'";
        System.out.println(query);
        try{ // delete from customer
                con.setAutoCommit(false);
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
                con.commit();
                con.setAutoCommit(true);
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in clothing db: "+ex);

                try{
                    rc=-1;
                    con.rollback();
                    con.setAutoCommit(true);
                }
                catch(Exception ex1)
                {
                    System.out.println("Error rolling back" + ex1);
                }
        }
        return(rc);
    }

	public Clothing findClothing(String barcode, boolean retriveAssociation)
    {
        String wClause = "  product.barcode = '" + barcode + "'";
        return singleWhere(wClause, retriveAssociation);
    }

	public int updateClothing(Clothing clo)
	{
		Clothing clothingObj = clo;

		int rc = -1;

		String query = "UPDATE product SET "
				+ "name ='" + clothingObj.getName() + "', "
				+ "purchasePrice = " + clothingObj.getPurchasePrice() + ", "
				+ "salesPrice = " + clothingObj.getSalesPrice() + ", "
				+ "rentPrice = " + clothingObj.getRentPrice() + ", "
				+ "countryOfOrigin = '" + clothingObj.getCountryOfOrigin() + "', "
				+ "minStock =" + clothingObj.getMinStock() + ", "
				+ "maxStock =" + clothingObj.getMaxStock() + ", "
				+ "currentStock =" + clothingObj.getCurrentStock() + ", "
				+ "supplierID =" + clothingObj.getSupplier().getSupplierID() + " "
				+ " WHERE barcode = '" + clothingObj.getBarCode() + "'";

		String query1 = "UPDATE clothing SET "
				+ "size ='" + clothingObj.getSize() + "', "
				+ "colour ='" + clothingObj.getColour() + "'"
				+ " WHERE barcode = '" + clothingObj.getBarCode() + "'";

		System.out.println("Update query:" + query + query1);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			rc = stmt.executeUpdate(query1);

			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Update exception in Clothing db: " + ex);
		}
		return (rc);
	}

	public ArrayList<Clothing> getAllClothing(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	public Clothing searchClothingName(String attValue, boolean retriveAssociation)
	{
		String wClause = "name like '%" + attValue + "%'";
		System.out.println("SearchClothing " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}

	public int insertClothing(Clothing clo)
    {
        //call to get the next GunReplica Barcode
        int nextBarCode = GetMax.getMaxId("Select max(barCode) from Clothing");
        nextBarCode = nextBarCode + 1;
        System.out.println("next Clothing Barcode = " +  nextBarCode);

        int rc = -1;
		String query="INSERT INTO Product(barCode, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, supplierID)  VALUES('"+
	  	nextBarCode + "','" +
                clo.getName()  + "',"  +
				clo.getPurchasePrice()  + ","  +
                clo.getSalesPrice() + "," +
                clo.getRentPrice() + ",'" +
                clo.getCountryOfOrigin() + "'," +
				clo.getMinStock() + "," +
				clo.getMaxStock() + "," +
				clo.getCurrentStock() + "," +
				clo.getSupplier().getSupplierID() + ")";
		String query1="INSERT INTO Clothing(barcode, size, colour)  VALUES('"+
	  	nextBarCode + "','" +
				clo.getSize() + "','" +
				clo.getColour() + "')";
                
       System.out.println("insert : " + query + query1);
       try{ // insert new GunReplica
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
		  rc = stmt.executeUpdate(query1);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("Clothing ikke oprettet");
          System.out.println("Insert exception in Clothing db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
        return(rc);
    }

	private Clothing singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Clothing ClothingObj = new Clothing();

	        String query =  buildQuery(wClause);
            System.out.println(query);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            ClothingObj = buildClothing(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {
                                int supplierID = ClothingObj.getSupplier().getSupplierID();
                                DBSupplier dbSupplier = new DBSupplier();
                                Supplier supplier = dbSupplier.findSupplier(supplierID, false);
                                ClothingObj.setSupplier(supplier);
                                String countryCode = ClothingObj.getCountryOfOrigin();
                                DBCountry dbCount = new DBCountry();
                                String countryName = dbCount.getCountryName(countryCode);
                                ClothingObj.setCountryOfOrigin(countryName);
                            }
			}
                        else{
                            ClothingObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return ClothingObj;
	}

	private ArrayList<Clothing> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Clothing> list = new ArrayList<Clothing>();
		String query = buildQuery(wClause);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next())
			{
				Clothing clothingObj = new Clothing();
				clothingObj = buildClothing(results);
				list.add(clothingObj);
			}

			stmt.close();

			if (retrieveAssociation)
			{
				DBSupplier DBsupplier = new DBSupplier();
				for (Clothing clothingObj : list)
				{
						clothingObj.setSupplier(DBsupplier.findSupplier(results.getInt("supplierID"), false));
						String countryCode = clothingObj.getCountryOfOrigin();
						DBCountry dbCount = new DBCountry();
						String countryName = dbCount.getCountryName(countryCode);
						clothingObj.setCountryOfOrigin(countryName);
				}
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private String buildQuery(String wClause)
	{
		String query="SELECT product.barcode, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, maxStock, currentStock, supplierID, size, colour   FROM product, clothing";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Clothing buildClothing(ResultSet results)
      {   Clothing ClothingObj = new Clothing();
          try{ // the columns from the table product  are used
		ClothingObj.setBarCode(results.getString("barcode"));
                ClothingObj.setName(results.getString("name"));
                ClothingObj.setPurchasePrice(results.getFloat("purchasePrice"));
                ClothingObj.setSalesPrice(results.getFloat("salesPrice"));
                ClothingObj.setRentPrice(results.getFloat("rentPrice"));
		ClothingObj.setCountryOfOrigin(results.getString("countryOfOrigin"));
		ClothingObj.setMinStock(results.getInt("minStock"));
		ClothingObj.setMaxStock(results.getInt("maxStock"));
                ClothingObj.setCurrentStock(results.getInt("currentStock"));
		ClothingObj.setSupplier(new Supplier(results.getInt("supplierID")));
		ClothingObj.setSize(results.getString("size"));
		ClothingObj.setColour(results.getString("colour"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the clothing object");
         }
         return ClothingObj;
      }
}
